package com.chen.q10;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * JDBC操作工具类
 */
@Component
public class JDBCUtil{
    @Autowired
    private DataSource dataSource;

    private static final String url = "jdbc:mysql://cdb-oj3adz5u.cd.tencentcdb.com:10051/geeklearn";
    private static final String username = "root";
    private static final String password = "cheng#123";
    private static final String driver = "com.mysql.jdbc.Driver";

    static {
//        try {
//            Class.forName(driver);
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
    }

    /**
     * 获取Connection，创建连接
     * @return
     * @throws SQLException
     */
    public Connection getConnection() throws SQLException {
//        Connection connection = DriverManager.getConnection(url,username,password);
        Connection connection = dataSource.getConnection();
        return connection;
    }

    /**
     * 断开Connection，释放资源
     * @param connection
     */
    public static  void disConnect(Connection connection){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 释放ResultSet
     * @param resultSet
     */
    public static  void release(ResultSet resultSet){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 释放Statement
     * @param statement
     */
    public static  void release(Statement statement){
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 释放PreparedStatement
     * @param preparedStatement
     */
    public static  void release(PreparedStatement preparedStatement){
        if (preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 增删改操作
     * @param sql
     * @return
     */
    public int insertOrUpdate(String sql) {
        int result = 0;
        Connection conn = null;
        Statement statement = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            statement = conn.createStatement();
            result = statement.executeUpdate(sql);
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            release(statement);
            disConnect(conn);
        }
        return result;
    }

    /**
     * 查询列表
     * @param sql
     * @param clazz
     * @param <T>
     * @return
     */
    public <T> List<T> list(String sql,Class<T> clazz){
        List<T> result = new ArrayList();
        Connection conn = null;
        Statement smt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            smt = conn.createStatement();
            rs = smt.executeQuery(sql);
            while (rs.next()){
                T obj = clazz.newInstance();
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields){
                    setField(obj,field,rs);
                }
                result.add(obj);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            release(rs);
            release(smt);
            disConnect(conn);
        }
        return result;
    }

    /**
     * 反射赋值
     * @param obj
     * @param field
     * @param rs
     */
    private static void setField(Object obj,Field field, ResultSet rs) {
        field.setAccessible(true);
        String fieldName = field.getName();
        Type type = field.getType();
        String typeName = type.getTypeName();
        try {
            if ("int".equals(typeName)||"java.lang.Integer".equals(typeName)){
                Integer value = rs.getInt(fieldName);
                if (value != null){
                    field.set(obj,value);
                }

            }else if ("java.lang.String".equals(typeName)){
                String value = rs.getString(fieldName);
                if (value != null){
                    field.set(obj,value);
                }
            }else {
                //todo 未完待续
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取PreparedStatement
     * PreparedStatement + 批处理方式
     * @param sql
     * @param autoCommit
     * @param sql
     * @return
     */
    public PreparedStatement getPreparedStatement(Connection conn, boolean autoCommit, String sql) {
        PreparedStatement psmt = null;
        try {
            conn.setAutoCommit(autoCommit);
            psmt = conn.prepareStatement(sql);

        } catch (SQLException e){
            e.printStackTrace();
        }
        return psmt;
    }

}
